Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 32
Miscellaneous SQL Topics

To wrap up Part IV of this book, “Tuning SQL,” this chapter covers some miscellaneous topics. This chapter is mainly a collection of unrelated tips on ways to improve SQL statements in certain areas.

Here are some of the tips covered in this chapter:

  Table sequences. This section of the chapter looks at the Oracle sequence generator and how to take advantage of cached sequences for primary key values.
  Join performance. This section describes how best to optimize the performance of different join operations.
  Locking. This section describes locking and how best to take advantage of Oracle’s locking features.
  Array processing. This section looks at how to take advantage of the Oracle array processing feature.

Although these topics are not large enough to warrant their own chapters, they are no less important than the other SQL tuning topics described in Part IV of this book.

Table Sequences

It is frequently necessary to generate a sequence of numbers to use in your database. You many need these numbers to identify a particular record or for some other purpose. To create a unique sequence of numbers on your own, you would have to lock the record that has the last value of the sequence, generate a new value, and then unlock the record. To avoid locking these records, Oracle provides a sequence generator that performs this service for you.

The Oracle sequence generator can generate sequential numbers of up to 38 digits, without having to manually lock records. When you define a sequence, you can specify the original values of the sequence, whether the sequence should be cached, and whether the sequence should be ascending or descending.

Creating Sequences

Sequences are created with the CREATE SEQUENCE command. This command has the following syntax:

CREATE SEQUENCE dog_breeds
  INCREMENT BY 1
  START WITH 1
 NOMAXVALUE
 NOCYCLE
  CACHE 4;

The following chart provides brief descriptions of the parameters.


Parameter Description

INCREMENT BY Specifies the amount to increment the sequence by each time a value is obtained.
START WITH Specifies the starting value.
MAXVALUE n Specifies the maximum value that the sequence can obtain.
NOMAXVALUE (Default) Specifies no maximum value for a sequence. The sequence can grow to 1027 for ascending sequences and –1 for descending sequences.
MINVALUE n Specifies the minimum value of the sequence.
NOMINVALUE (Default) Specifies no minimum value for a sequence. The sequence can have a minimum of 1 for ascending sequences and –1026 for descending sequences.
CYCLE Specifies that a sequence will restart after reaching the maximum or minimum value.
NOCYCLE (Default) Specifies that the sequence cannot recycle after reaching the maximum or minimum value.
CACHE n Specifies the number of sequence entries to cache for quick access. (The default is 20 values.)
NOCACHE Specifies that no sequence entries should be cached.
ORDER Specifies that sequence entries are generated in the order in which they are requested. By default, this is not the case.
NOORDER (Default) Specifies that sequence numbers are not necessarily generated in the order in which they are requested. This is usually fine for primary key values.

Tuning Sequences

To get the best performance out of sequences, you should cache as many sequences as you think you will have simultaneous requests for them. By over-specifying the number of cached sequences, you use more memory than necessary. By under-specifying the number of cached entries, you cause undo waiting for the sequences.

The Oracle sequence generator is much more efficient than any way you can manually generate sequences. If you have a series of values that must be sequential, I recommend using the Oracle sequence generator.

Using Sequences

To generate a new sequence value, simply reference the value of sequence_name.NEXTVAL. To re-reference that number from within the same SQL block, simply reference the value of sequence_name.CURVAL. When you reference sequence_name.NEXTVAL, a new sequence number is generated.

Here is an example using the sequence created in the preceding section to generate a new value in the BREEDS table. The result of this INSERT statement is to insert a breed number that has a sequentially growing value in the BREED column. (Remember that the BREED column of the BREEDS table is a sequential value for the dog breed. This value is referenced by the BREED column in the DOGS table.)

SQL> INSERT INTO breeds
  2  ( breed, breed_name, description )
 3  VALUES
  4  ( dog_breeds.NEXTVAL, 'Australian Cattle Dog', 'Hard working and tough' );

1 row created.

Sequences are incremented as they are accessed, independent of rollback or commit. If a transaction generates a sequence and then rolls back, the sequence is not replaced. Therefore, there may be holes in your sequential values. This is usually not a problem.


NOTE:  Because sequences are generated independently of commits or rollbacks, you may have gaps in the sequences. Although this is usually not an issue, you should make a note of it.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.